#encoding:utf-8

import logging
import torndb

import config

'''数据库处理类'''
class Database():
    def __init__(self, fetchDirector, parent = None):
        self.fetchDirector = fetchDirector
        self.mysqlHost = config.MYSQL_HOST
        self.mysqlDb = config.MYSQL_DB
        self.mysqlUser = config.MYSQL_USER
        self.mysqlPasswd = config.MYSQL_PASSWD

        self.db = torndb.Connection(host = self.mysqlHost, database = self.mysqlDb, user = self.mysqlUser, password = self.mysqlPasswd)

    '''插入字典类型数据到数据库中，这个函数主要是封装数据库操作以方便写字典类型，可以放在torndb中'''
    def insertByDict(self, tablename, rowdict, replace = False):
        cursor = self.db._cursor()
        cursor.execute("describe %s" % tablename)
        allowed_keys = set(row[0] for row in cursor.fetchall())
        keys = allowed_keys.intersection(rowdict)
    
        if len(rowdict) > len(keys):
            unknown_keys = set(rowdict) - allowed_keys
            logging.error("skipping keys: %s", ", ".join(unknown_keys))
    
        columns = ", " . join(keys)
        values_template = ", " . join(["%s"] * len(keys))
    
        if replace:
            sql = "REPLACE INTO %s (%s) VALUES (%s)" % (tablename, columns, values_template)
        else:
            sql = "INSERT INTO %s (%s) VALUES (%s)" % (tablename, columns, values_template)
    
        values = tuple(rowdict[key] for key in keys)
        
        try:
            cursor.execute(sql, values)
            return cursor.lastrowid
        finally:
            cursor.close()
    
    '''插入分类到数据库中，加了个异常处理'''
    def insertCategoryByDict(self, categoryDict, replace = False):
        try:
            id = self.insertByDict("category", categoryDict, replace)
            return id
        except Exception, ex:
            logging.error("Insert category failed! Exception: %s   Category: %s", str(ex), str(categoryDict))
            return None

    '''插入figure到数据库中，加了个异常处理'''
    def insertFigureByDict(self, figureDict, replace = False):
        try:
            id = self.insertByDict("figure", figureDict, replace)
            return id
        except Exception, ex:
            logging.error("Insert figure failed! Exception: %s   Category: %s", str(ex), str(figureDict))
            return None

    '''插入评论到数据库中，加了个异常处理'''
    def insertCommentByDict(self, commentDict, replace = False):
        try:
            id = self.insertByDict("comment", commentDict, replace)
            return id
        except Exception, ex:
            logging.error("Insert comment failed! Exception: %s   Category: %s", str(ex), str(commentDict))
            return None
    
    '''判断分类是否存在，实际上可以不要name'''
    def if_category_exist(self, name, title):
        ret = self.get_category_by_name(name)
        if ret != None:
            return True

        ret = self.get_category_by_title(title)
        if ret != None:
            return True

        return False
    
    '''通过name获取分类'''
    def get_category_by_name(self, name):
        sql = "select * from category where name = '" + str(name) + "'"
        return self.db.get(sql)   

    '''通过title获取分类'''
    def get_category_by_title(self, title):
        sql = "select * from category where title = '" + str(title) + "'"
        return self.db.get(sql)

    '''获取全部分类，测试用'''
    def get_category_all(self):
        sql = "select * from category"
        return self.db.query(sql)
    
    def if_figure_exist(self, imageID):
        ret = self.get_figure_by_imageID(imageID)
        if ret != None:
            return True

        return False
    
    '''通过imageID获取figure数据'''
    def get_figure_by_imageID(self, imageID):
        sql = "select * from figure where imageID = '" + str(imageID) + "'"
        return self.db.get(sql) 
    
    def if_comment_exist(self, _id):
        ret = self.get_comment_by__id(_id)
        if ret != None:
            return True

        return False
    
    '''通过_id获取评论'''
    def get_comment_by__id(self, _id):
        sql = "select * from comment where _id = '" + str(_id) + "'"
        return self.db.get(sql) 
